﻿Imports Npgsql

Public Class TicketDashBoard
    Dim dsTickets As New DataSet
    Dim dvTickets As New DataView

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        '
        ' Close the Technician Dashboard form
        '
        Me.Close()
    End Sub

    Private Sub TicketDashBoard_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cbFilter.Items.Add("Show Open Tickets")
        cbFilter.Items.Add("Show Only My Tickets")
        cbFilter.Items.Add("Show Closed Tickets")
        cbFilter.Items.Add("Show Knowledge Base Tickets")
        cbFilter.SelectedIndex = 0

        dgTickets.AutoResizeColumns()
    End Sub

    Private Sub DisplayTickets(ByVal RowFilter As String, ByVal SearchText As String)
        dgTickets.AutoGenerateColumns = False
        If SearchText = "" Then
            dsTickets = retrieveTickets()
        Else
            dsTickets = retrieveTickets(SearchText)
        End If

        dsTickets.Tables(0).Columns.Add("submittedby", GetType(System.String), "lastname + ', ' + firstname")

        dvTickets.Table = dsTickets.Tables(0)
        dvTickets.RowFilter = RowFilter
        dgTickets.DataSource = dvTickets
    End Sub

    Private Function retrieveTickets() As DataSet
        ' Create the Data set
        Dim ds As New DataSet()

        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "SELECT * FROM public.ticket INNER JOIN public.issuetypedef ON ticket.issuetypeid = issuetypedef.issuetypeid INNER JOIN public.ticketpriority ON ticket.priorityid = ticketpriority.priorityid INNER JOIN public.users ON ticket.submitterid = users.userid"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            da.Fill(ds)

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try

        Return ds
    End Function
    Private Function retrieveTickets(ByVal searchText As String) As DataSet
        ' Create the Data set
        Dim ds As New DataSet()

        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "Select * From public.ticket INNER JOIN public.issuetypedef ON ticket.issuetypeid = issuetypedef.issuetypeid " & _
                                "INNER JOIN public.ticketpriority ON ticket.priorityid = ticketpriority.priorityid " & _
                                "INNER JOIN public.users ON ticket.submitterid = users.userid " & _
                                "inner join (SELECT TBL.TICKETID FROM (SELECT ticket.ticketid FROM public.ticket " & _
                                "LEFT OUTER JOIN public.ticketnotes ON ticket.ticketid = ticketnotes.ticketid " & _
                                "WHERE public.ticket.subject like '%" + searchText + "%' or public.ticket.subjectdesc like '%" + searchText + "%' or public.ticketnotes.note like '%" + searchText + "%') As TBL " & _
                                "GROUP BY TBL.TICKETID) TBL02 on ticket.ticketid = tbl02.ticketid"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            da.Fill(ds)

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try

        Return ds
    End Function

    Private Sub FilterBoxes(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRefresh.Click, btnSearch.Click, cbFilter.SelectedIndexChanged
        Select Case cbFilter.Text
            Case "Show Only My Tickets"
                txtSearchText.Enabled = False
                btnSearch.Enabled = False
                DisplayTickets(String.Format("username = '{0}'", clsUIBase.AuthenticatedUser.UserName), "")
            Case "Show Knowledge Base Tickets"
                txtSearchText.Enabled = True
                btnSearch.Enabled = True
                DisplayTickets(String.Format("knowledgebase = '{0}'", True), txtSearchText.Text)
            Case "Show Open Tickets"
                txtSearchText.Enabled = False
                btnSearch.Enabled = False
                DisplayTickets("dateresolved is NULL", "")
            Case "Show Closed Tickets"
                txtSearchText.Enabled = False
                btnSearch.Enabled = False
                DisplayTickets("dateresolved IS NOT NULL", "")
        End Select
    End Sub
    Private Sub dgTickets_CellContentDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgTickets.CellContentDoubleClick
        '
        '
        '
        Dim frmTicketDetail As New TicketDetail

        frmTicketDetail.getTicketID = dgTickets.Rows(dgTickets.CurrentRow.Index).Cells(0).Value()
        frmTicketDetail.ShowDialog()
    End Sub

    Private Sub IssueTypesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles IssueTypesToolStripMenuItem.Click
        '
        '
        '
        Dim frmIssueTypes As New IssueTypes

        frmIssueTypes.ShowDialog()
    End Sub

    Private Sub PrintBooksToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles IssuePriorityTypesToolStripMenuItem.Click
        '
        '
        '
        Dim frmPriorityTypes As New PriorityTypes

        frmPriorityTypes.ShowDialog()
    End Sub

    Private Sub btnNewTicket_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewTicket.Click
        '
        '
        '
        Dim frmTicketDetail As New NewTicket

        frmTicketDetail.ShowDialog()
    End Sub

    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Me.Close()
    End Sub

    Private Sub NewUserToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewUserToolStripMenuItem.Click
        Dim frmNewUser As New NewUser
        frmNewUser.ShowDialog()
    End Sub

    Private Sub EditUserToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim frmEditUser As New EditUser
        frmEditUser.ShowDialog()
    End Sub

    Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click
        Dim frmAbout As New AboutBox
        frmAbout.ShowDialog()
    End Sub

    Private Sub ToolStripMenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem2.Click
        Dim frmEdituser As New EditUser
        frmEdituser.ShowDialog()
    End Sub
End Class